import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.junit.Test;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.tdcy.biz.app.dao.eo.DeviceEO;
import com.tdcy.biz.app.service.IDeviceSvc;
import com.tdcy.biz.nt.dao.eo.MachineTypeEO;
import com.tdcy.framework.util.DateUtils;
import com.tdcy.framework.util.StringUtils;
import com.tdcy.sys.dao.ICommonDAO;

@org.junit.runner.RunWith(SpringJUnit4ClassRunner.class)
@org.springframework.test.context.ContextConfiguration(locations = "classpath:application.xml")
public class InitMachineInfo {
	@Resource
	ICommonDAO commondao;

	@Resource
	IDeviceSvc devicesvc;

	List<MachineTypeEO> typelist = null;

	@Test
	public void test1() throws Exception {
		deleteAll();
		findAllMachineTypeEO();
		handleExcel();
	}

	public void findAllMachineTypeEO() {
		String sql = "select t.* from nt_machine_type t ";
		typelist = commondao.execSqlQuery(MachineTypeEO.class, sql);
	}

	public MachineTypeEO findMachineTypeEO(String typename) {
		for (MachineTypeEO mbeo : typelist) {
			if (typename.equals(mbeo.getMtName())) {
				return mbeo;
			}
		}

		return null;
	}

	public void handleExcel() throws IOException {
		InputStream is = new FileInputStream(new File("d://test.xls"));
		POIFSFileSystem fs = new POIFSFileSystem(is);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		HSSFRow row = sheet.getRow(0);
		for (int i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			String date = parseExcel(row.getCell(1)).trim().toLowerCase();
			String sn = parseExcel(row.getCell(2)).trim();
			String mac = parseExcel(row.getCell(5)).trim();
			String xh = parseExcel(row.getCell(6)).trim();

			System.out.println(sn + ":" + mac + ":" + xh);
			if (StringUtils.isEmpty(sn)) {
				continue;
			}
			//
			MachineTypeEO mteo = findMachineTypeEO(xh);

			DeviceEO meo = new DeviceEO();
			meo.setDevice2code(sn);
			meo.setDeviceMac(mac);
			meo.setDeviceSn(sn);
			meo.setDeviceProducttime(DateUtils.toDate(date, "yyyy-MM-dd"));
			meo.setDeviceShipmenttime(DateUtils.toDate(date, "yyyy-MM-dd"));
			if (mteo != null) {
				meo.setMtypeId(mteo.getMtypeId());
				meo.setMserialId(mteo.getMserialId());

			}
			devicesvc.addDevice(meo);
		}
	}

	private String parseExcel(Cell cell) {
		if (cell == null) {
			return "";
		}
		String result = new String();
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
				SimpleDateFormat sdf = null;
				if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
					sdf = new SimpleDateFormat("HH:mm");
				} else {// 日期
					sdf = new SimpleDateFormat("yyyy-MM-dd");
				}
				Date date = cell.getDateCellValue();
				result = sdf.format(date);
			} else if (cell.getCellStyle().getDataFormat() == 58) {
				// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				double value = cell.getNumericCellValue();
				Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
				result = sdf.format(date);
			} else {
				double value = cell.getNumericCellValue();
				CellStyle style = cell.getCellStyle();
				DecimalFormat format = new DecimalFormat();
				String temp = style.getDataFormatString();
				// 单元格设置成常规
				if (temp.equals("General")) {
					format.applyPattern("#");
				}
				result = format.format(value);
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:// String类型
			result = cell.getRichStringCellValue().toString();
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			result = "";
		default:
			result = "";
			break;
		}
		return result;
	}

	private String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}

	private String getStringCellValue(XSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}

	public void deleteAll() {
		String sql = "delete from app_device where device_sn != 'WF06170329A004'";
		commondao.execSqlUpdate(sql);
	}
}
